/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @authors: Pat, Jamie, Kazu, Stephen, and Shaoyan
 */
import java.sql.*;

/**
 * 
 * @authors: Pat, Jamie, Kazu, Stephen, and Shaoyan
 */
public class Depot {

    private DB db;
    
    /**
     * 
     */
    public Depot() {
        this.db = new DB();
    }
    /**
     * 
     * @param depotName
     * @param address
     */
    public void addDepot(String depotName, String address, String phoneNumber){
        Connection conn = db.connect();
        String query = "INSERT INTO DepotDB (depotName,address,phoneNumber) "
                +"VALUES ('" + depotName + "','" + address +"','"+phoneNumber+ "')";
        db.putQuery(conn, query);
        db.disconnect(conn);
    }
    
    /**
     * 
     * @param depotID
     */
    public void removeDepot(int depotID){
        Connection conn = db.connect();
        String query = "DELETE FROM DepotDB WHERE depotId = " + depotID;
        db.putQuery(conn, query);
        db.disconnect(conn);
    }
   
    /**
     * 
     * @param depotID
     * @return
     */
    public String getDepotName(int depotID) {
        String depotName = "";
        Connection conn = db.connect();
        String query = "SELECT depotName FROM DepotDB WHERE depotId="+depotID;
        ResultSet result = db.getQuery(conn,query);
        try{
            if (result.next()) {
                depotName = result.getString("depotName");
            }
        }catch(Exception e){
            System.out.println( e.getMessage());
        }
        db.disconnect(conn);
        
        return depotName;
    }
    public void updateDepot(int depotId,String name, String address, String phone){
        Connection _conn = db.connect();
        String query = "UPDATE DepotDB SET depotName = '" + name 
                +"', address = '"+address
                +"', phoneNumber = '" +phone
                +"' WHERE depotId = " + depotId;
        db.putQuery(_conn, query);
        db.disconnect(_conn);
    }
    public String[] getDepotInfo(int depotID){
        String[] depotinfo = new String[3];
        Connection conn = db.connect();
        String query = "SELECT * FROM DepotDB WHERE depotId="+depotID;
        ResultSet result = db.getQuery(conn,query);
        try{
            if (result.next()) {
                depotinfo[0] = result.getString("depotName");
                depotinfo[1] = result.getString("address");
                depotinfo[2] = result.getString("phoneNumber");
            }
        }catch(Exception e){
            System.out.println( e.getMessage());
        }
        db.disconnect(conn);
        
        return depotinfo;
    }
    public int getDepotID(String name, String address){
        int depotID= -1;
        Connection conn = db.connect();
        
        String query = "SELECT depotId FROM DepotDB WHERE depotName="+"'"+name+"' OR address= '"+address+"'";
        ResultSet result = db.getQuery(conn,query);
        try{
            if (result.next()) {
                depotID = result.getInt("depotId");
            }
        }catch(Exception e){
            System.out.println( e.getMessage());
        }
        System.out.println(name+" and "+address+" and " +depotID);
        db.disconnect(conn);
        return depotID;
    }
}
